﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DaInterFace;

namespace DaFunction
{
    internal class DataBase : IDataBase
    {
        private Dictionary<String, Account> Accounts = new Dictionary<String, Account>();
        private Dictionary<String, SetOfBook> SetOfBooks = new Dictionary<String, SetOfBook>();
        private String _connection;

        public event DataBaseMessage MessageEvent;

        //string strAccountISN = "";
        //字典—销售账户
        //public static Dictionary<string, SalesAccount> dsa = new Dictionary<string, SalesAccount>();

        public DataBase(String connection)
        {
            _connection = connection;
            FillAccounts();
        }
        public String GetConnection()
        {
            return _connection;
        }
        public Boolean Test()
        {
            try
            {
                //SqlParameter[] sps = new SqlParameter[1];
                //sps[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
                //sps[0].Value = "0000";

                //String sql = "SELECT FText Pswd,FCheckBox1 IMEI FROM KEW_SalesGuests WHERE FCheckBox=1 AND FBillNo=@UserName\r\nSELECT FText2 IMEI FROM KEW_SalesGuestsIMEI WHERE FID =(SELECT FID FROM KEW_SalesGuests WHERE FCheckBox=1 AND FBillNo=@UserName)";
                //Recordset rs = ExeProcedure("2", sql, sps);


                DataSet data = new DataSet();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT GetDate() ServerTime";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DateTime now = DateTime.Now;
                adapter.Fill(data);
                String time = data.Tables[0].Rows[0]["ServerTime"].ToString();

                String note = "{主服务器连接正常} ";
                
                foreach (Account account in Accounts.Values)
                {
                    try
                    {
                        now = DateTime.Now;
                        cmd.Connection = conn;
                        adapter = new SqlDataAdapter(cmd);
                        adapter.Fill(data);
                        note += String.Format("[{0}连接正常] ",account.Name);
                    }
                    catch (Exception ex)
                    {
                        note += String.Format("[{0}连接失败:{1}] ", account.Name, ex.Message);
                    }
                }
                Message(MessageType.Note, note, (DateTime.Now - now).TotalMilliseconds);
                return true;
            }
            catch (Exception ex)
            {
                Message(MessageType.Error, ex.Message, 0);
                return false;
            }
        }

        public void Message(MessageType type, String note)
        {
            Message(type, note, 0);
        }

        private void Message(MessageType type, String note, Double span)
        {
            if (MessageEvent != null)
            {
                MessageEvent.Invoke(type, note, span);
            }
        }

        public Dictionary<String, SetOfBook> GetAccounts()
        {
            if (SetOfBooks.Count == 0) FillAccounts();
            return SetOfBooks;
        }

        private void FillAccounts()
        {
            try
            {
                DataSet data = new DataSet();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT A.FText BaseName,A.FText1 IPAdd,A.FText2 DataUser,A.FText3 DataPswd,A.FNumber,A.FName,A.FID FROM KEW_DataBaseEntry A";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(data);
                foreach (DataRow reader in data.Tables[0].Rows)
                {
                    Account account = new Account();
                    SetOfBook book = new SetOfBook();
                    account.ID = reader["FNumber"].ToString();
                    account.Name = reader["FName"].ToString();
                    account.ConnectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Connect Timeout=15",
                        reader["IPAdd"].ToString(), reader["BaseName"].ToString(), reader["DataUser"].ToString(), reader["DataPswd"].ToString());

                    Accounts.Add(reader["FNumber"].ToString(), account);

                    book.FID = reader["FID"].ToString();
                    book.FNumber = reader["FNumber"].ToString();
                    book.FName = reader["FName"].ToString();
                    SetOfBooks.Add(book.FID, book);
                }
            }
            catch (Exception ex)
            {
                Message(MessageType.Error, ex.Message, 0);
            }
        }
        public Recordset ExeBase(String sql)
        {
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(recordset.Data);

                recordset.Enable = true;
                recordset.Message = "";
                Message(MessageType.Note, sql + pContent, (DateTime.Now - curtime).TotalMilliseconds);
            }
            catch (Exception ex)
            {
                recordset.Enable = false;
                recordset.Message = sql + "   :" + ex.Message;
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }

        public Recordset ExeBase(String sql, SqlParameter[] parameters)
        {
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        pContent += String.Format("\r\n[{0}]:{1}", parameters[i].ParameterName, parameters[i].Value);
                    }
                }
            
                cmd.Connection = conn;
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(recordset.Data);

                recordset.Enable = true;
                recordset.Message = "";
                Message(MessageType.Note, sql + pContent, (DateTime.Now - curtime).TotalMilliseconds);
            }
            catch (Exception ex)
            {
                recordset.Enable = false;
                recordset.Message = sql + "   :" + ex.Message;
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }

        public Recordset ExeProcedure(String accountNo, String sql)
        {
            if (Accounts.Count == 0) FillAccounts();
            DateTime curtime = DateTime.Now;
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();
            if (Accounts.ContainsKey(accountNo))
            {
                Account account = Accounts[accountNo];
                
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, new SqlConnection(account.ConnectionString));
                    adapter.Fill(recordset.Data);
                    recordset.Enable = true;

                    Message(MessageType.Note, sql, (DateTime.Now - curtime).TotalMilliseconds);
                }
                catch (Exception ex)
                {
                    recordset.Enable = false;
                    recordset.Message = sql + "   :" + ex.Message;
                    Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
                }
            }
            else
            {
                recordset.Enable = false;
                recordset.Message = "错误的账套号：" + accountNo;
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }

        public Recordset ExeProcedure(String accountNo, String sql, SqlParameter[] parameters)
        {
            if (Accounts.Count == 0)
             FillAccounts();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();

            if (Accounts.ContainsKey(accountNo))
            {
                Account account = Accounts[accountNo];
                SqlCommand cmd = new SqlCommand();
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);

                    for (int i = 0; i < parameters.Length; i++)
                    {
                        pContent += String.Format("\r\n[{0}]:{1}", parameters[i].ParameterName, parameters[i].Value);
                    }
                }
                cmd.Connection = new SqlConnection(account.ConnectionString);
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                try
                {
                    adapter.Fill(recordset.Data);
                    recordset.Enable = true;
                    Message(MessageType.Note, sql + pContent, (DateTime.Now - curtime).TotalMilliseconds);
                }
                catch (Exception ex)
                {
                    recordset.Enable = false;
                    recordset.Message = sql + "    :" + ex.Message;
                    Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
                }

                adapter.Dispose();
                adapter = null;
                cmd.Dispose();
                cmd = null;
            }
            else
            {
                recordset.Enable = false;
                recordset.Message = "错误的账套号";
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }

        public MeterInfoObject GetMeterInfoByCardSerial(String serial)
        {
            MeterInfoObject mio = new MeterInfoObject();
            String sql = "";
            try
            {
                Recordset baseRs = ExeBase(String.Format("SELECT B.FID BaseID,B.FNumber BaseNumber,B.FName BaseName, A.FID CardID, A.FNumber CardNo,A.FComboBox BillTarg,A.FText1 OrderID,A.FText2 BillID,A.FCheckBox IsLong,A.FCheckBox2 IsMetering FROM KEW_ICCardEntry A LEFT JOIN KEW_DataBaseEntry B ON A.FBase=B.FID WHERE A.FText='{0}'", serial));
                if (baseRs.Enable)
                {
                    if (baseRs.Data.Tables[0].Rows.Count == 1)
                    {
                        DataRow baseRow = baseRs.Data.Tables[0].Rows[0];
                        mio.Base = new ItemObject(baseRow["BaseID"].ToString(), baseRow["BaseNumber"].ToString(), baseRow["BaseName"].ToString());
                        mio.Card = new ItemObject(baseRow["CardID"].ToString(), baseRow["CardNo"].ToString(), baseRow["CardNo"].ToString());
                        mio.Type = Common.CInt(baseRow["BillTarg"].ToString());
                        mio.IsLong = Common.CBool(baseRow["IsLong"].ToString());
                        mio.IsMetering = Common.CBool(baseRow["IsMetering"].ToString());
                        mio.BillID = baseRow["BillID"].ToString();


                        if (mio.IsLong && !mio.IsMetering && (mio.Type == 0 || mio.Type == 1))
                        {
                            mio.OrderID = baseRow["OrderID"].ToString();

                            switch (mio.Type)
                            { 
                                case 0:
                                    sql = String.Format("SELECT FText CarNo,FID_SRC,FDecimal2 QtyIpt FROM KEW_SaleDeliveryCard WHERE FID={0}", mio.OrderID);
                                    break;
                                case 1:
                                    sql = String.Format("SELECT FText CarNo,FID_SRC,FDecimal2 QtyIpt FROM KEW_PurchDeliveryCard WHERE FID={0}", mio.OrderID);
                                    break;
                            }

                            Recordset InfoRs = ExeProcedure(mio.Base.Number, sql);
                            if (InfoRs.Enable)
                            {
                                if (InfoRs.Data.Tables[0].Rows.Count == 1)
                                {
                                    DataRow infoRow = InfoRs.Data.Tables[0].Rows[0];
                                    mio.CarNumber = infoRow["CarNo"].ToString();
                                    mio.SrcID = infoRow["FID_SRC"].ToString();
                                    mio.QtyIpt = Common.CDec(infoRow["QtyIpt"].ToString());
                                }
                                else
                                {
                                    mio.UnEnableReason = "该磁卡没有对应的信息";
                                }
                            }
                            else
                            {
                                mio.UnEnableReason = InfoRs.Message;
                            }
                            mio.InfoEnable = true;
                        }
                        else if (mio.IsMetering && mio.Type >= 0 && mio.Type <= 3)
                        {
                            switch (mio.Type)
                            {
                                case 0:
                                    sql += "SELECT A.FID BillID,A.FBillNo BillNumber,A.FComboBox Step,A.FText CarNumber,A.FDecimal4 QtyIpt,A.FDecimal QtyIns,A.FDecimal1 QtyOut,A.FDecimal2 QtySut,A.FDecimal3 QtyAcc,A.FDecimal22 QtyHis,";
                                    sql += "A.FTime TimePln,A.FTime1 TimeIns,A.FTime2 TimeOut,A.FText1 EmpPln,A.FText2 EmpIns,A.FText3 EmpOut,A.FText9 EmpChk,";
                                    sql += "B.FID PID,B.FNumber PNumber,B.FName PName,B.FDecimal2 PPrice,B.FDecimal PQty,C.FItemID TID,C.FNumber TNumber,C.FName TName,D.FItemID GID,D.FNumber GNumber,D.FName GName ";
                                    sql += "FROM KEW_SalesInfo A LEFT JOIN KEW_ProductsEntry B ON A.FBase=B.FID LEFT JOIN t_ICItem C ON B.FBase=C.FItemID LEFT JOIN t_Organization D ON A.FBase1=D.FItemID WHERE A.FCheckBox4=0 AND A.FID='{0}' AND FCheckBox4=0";
                                    break;
                                case 1: sql += "SELECT A.FID BillID,A.FBillNo BillNumber,A.FComboBox Step,A.FText CarNumber,A.FDecimal4 QtyIpt,A.FDecimal QtyIns,A.FDecimal1 QtyOut,A.FDecimal2 QtySut,A.FDecimal3 QtyAcc,A.FDecimal22 QtyHis,";
                                    sql += "A.FTime TimePln,A.FTime1 TimeIns,A.FTime2 TimeOut,A.FText1 EmpPln,A.FText2 EmpIns,A.FText3 EmpOut,A.FText9 EmpChk,";
                                    sql += "B.FID PID,B.FNumber PNumber,B.FName PName,0 PPrice,B.FDecimal PQty,C.FItemID TID,C.FNumber TNumber,C.FName TName,D.FItemID GID,D.FNumber GNumber,D.FName GName ";
                                    sql += "FROM KEW_PurchInfo A LEFT JOIN KEW_PurchItemEntry B ON A.FBase=B.FID LEFT JOIN t_ICItem C ON B.FBase=C.FItemID LEFT JOIN t_Supplier D ON A.FBase1=D.FItemID WHERE A.FCheckBox4=0 AND A.FID='{0}' AND FCheckBox4=0"; break;

                                case 2: sql += "SELECT A.FID BillID,A.FBillNo BillNumber,A.FComboBox Step,A.FText CarNumber,0 QtyIpt,A.FDecimal QtyIns,A.FDecimal1 QtyOut,A.FDecimal2 QtySut,A.FDecimal2 QtyAcc,0 QtyHis,";
                                    sql += "A.FTime TimePln,A.FTime1 TimeIns,A.FTime2 TimeOut,A.FText3 EmpPln,A.FText4 EmpIns,A.FText5 EmpOut,'' EmpChk,";
                                    sql += "'0' PID,'' PNumber,A.FText2 PName,0 PPrice,0 PQty,'0' TID,'' TNumber,A.FText2 TName,'0' GID,'' GNumber,A.FText1 GName ";
                                    sql += "FROM KEW_OtherInfo A WHERE A.FCheckBox1=0 AND A.FID='{0}' AND FCheckBox1=0"; break;

                                case 3: sql += "SELECT A.FID BillID,A.FBillNo BillNumber,A.FComboBox Step,A.FText CarNumber,0 QtyIpt,A.FDecimal QtyIns,A.FDecimal1 QtyOut,A.FDecimal2 QtySut,A.FDecimal2 QtyAcc,0 QtyHis,";
                                    sql += "A.FTime TimePln,A.FTime TimeIns,A.FTime1 TimeOut,'' EmpPln,A.FText1 EmpIns,A.FText2 EmpOut,'' EmpChk,";
                                    sql += "C.FItemID PID,C.FNumber PNumber,C.FName PName,0 PPrice,0 PQty,C.FItemID TID,C.FNumber TNumber,C.FName TName,'0' GID,'' GNumber,'' GName ";
                                    sql += "FROM KEW_StockInfo A LEFT JOIN t_ICItem C ON A.FBase2=C.FItemID WHERE A.FID='{0}'"; break;
                            }
                            Recordset InfoRs = ExeProcedure(mio.Base.Number, String.Format(sql, baseRow["BillID"].ToString()));
                            if (InfoRs.Enable)
                            {
                                if (InfoRs.Data.Tables[0].Rows.Count == 1)
                                {
                                    DataRow infoRow = InfoRs.Data.Tables[0].Rows[0];
                                    mio.BillNumber = infoRow["BillNumber"].ToString();
                                    mio.Step = Common.CInt(infoRow["Step"].ToString());
                                    mio.CarNumber = infoRow["CarNumber"].ToString();
                                    mio.QtyIpt = Common.CDec(infoRow["QtyIpt"].ToString());
                                    mio.QtyIns = Common.CDec(infoRow["QtyIns"].ToString());
                                    mio.QtyOut = Common.CDec(infoRow["QtyOut"].ToString());
                                    mio.QtySut = Common.CDec(infoRow["QtySut"].ToString());
                                    mio.QtyAcc = Common.CDec(infoRow["QtyAcc"].ToString());
                                    mio.QtyHis = Common.CDec(infoRow["QtyHis"].ToString());
                                    mio.TimePln = Common.CTime(infoRow["TimePln"].ToString());
                                    mio.TimeIns = Common.CTime(infoRow["TimeIns"].ToString());
                                    mio.TimeOut = Common.CTime(infoRow["TimeOut"].ToString());
                                    mio.EmpPln = infoRow["EmpPln"].ToString();
                                    mio.EmpIns = infoRow["EmpIns"].ToString();
                                    mio.EmpOut = infoRow["EmpOut"].ToString();
                                    mio.EmpChk = infoRow["EmpChk"].ToString(); 
                                    mio.Item = new ItemObject(infoRow["PID"].ToString(), infoRow["PNumber"].ToString(), infoRow["PName"].ToString());
                                    mio.SourceItem = new ItemObject(infoRow["TID"].ToString(), infoRow["TNumber"].ToString(), infoRow["TName"].ToString());
                                    mio.CurrentUnit = new ItemObject(infoRow["GID"].ToString(), infoRow["GNumber"].ToString(), infoRow["GName"].ToString());

                                    mio.ItemBal = Common.CDec(infoRow["PQty"].ToString());
                                    mio.ItemPrice = Common.CDec(infoRow["PPrice"].ToString());

                                    mio.InfoEnable = true;
                                }
                                else
                                {
                                    mio.UnEnableReason = "该磁卡没有对应的信息";
                                }
                            }
                            else
                            {
                                mio.UnEnableReason = InfoRs.Message;
                            }
                        }
                        else if (mio.Type >= 4 && mio.Type <= 5)
                        {
                            mio.InfoEnable = true;
                        }
                    }
                    else
                    {
                        mio.UnEnableReason = "没有找到该序列号对应的磁卡";
                    }
                }
                else
                {
                    mio.UnEnableReason = baseRs.Message;
                }
            }
            catch (Exception ex)
            {
                mio.UnEnableReason = ex.Message;
            }
            return mio;
        }
        public Dictionary<string, SalesAccount> ExeSalesAccount(String accountid, String strAccountISN)
        {
            //字典—销售账户
            Dictionary<string, SalesAccount> dsa = new Dictionary<string, SalesAccount>();
            if (Accounts.Count == 0)
                FillAccounts();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            Recordset recordset = new Recordset();
            DataSet data = new DataSet();
            if (Accounts.ContainsKey(accountid))
            {
                Account account = Accounts[accountid];
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = new SqlConnection(account.ConnectionString);
                cmd.CommandText = "SELECT FText1 ISN, FID AccountFID, FBillNo AccountNo From KEW_SalesAccount WHERE FText1 in (" + strAccountISN + ")";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                try
                {
                    adapter.Fill(data);
                    foreach (DataRow reader in data.Tables[0].Rows)
                    {
                        SalesAccount sa = new SalesAccount();
                        sa.AccountISN = reader["ISN"].ToString();
                        sa.AccountFID = reader["AccountFID"].ToString();
                        sa.AccountNo = reader["AccountNo"].ToString();
                        dsa.Add(sa.AccountISN, sa);

                    }
                }
                catch (Exception ex)
                {
                    dsa = null;
                    Message(MessageType.Error, ex.Message, (DateTime.Now - curtime).TotalMilliseconds);
                }
            }
            return dsa;
        }

    }

}
